﻿using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using MicroDal;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using MicroDal.Providers;

namespace MicroDalTests
{
    [TestClass]
    public class SqlParserTest
    {
        TestingResource testDbRes;

        [TestInitialize]
        public void Setup()
        {
            testDbRes = new TestingResource("");
        }

        [TestMethod]
        public void PropCommandType_SimpleSelect_Text()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("select * from aa where x=@1");

            Assert.IsTrue(sut.CommandType == CommandType.Text);
        }

        [TestMethod]
        public void InitializeParameters_DublicateParam_ParamLengthOne()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("select * from aa where x=@1 and y= @1");

            var parameters = sut.GetParameterEnumerator(1).ToArray();
            Assert.AreEqual(1, parameters.Length);
        }

        [TestMethod, Description("Passing null to InitializeParameter should set DbParameter to DbNull.Value")]
        public void InitializeParameters_Null_DbNullValue()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("@1");

            var parameters = sut.GetParameterEnumerator(null).ToArray();
            Assert.AreEqual(DBNull.Value, parameters[0].Value);
        }

        [TestMethod]
        public void InitializeParameters_IntAndNull_IntDbNullValue()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("@1 @2");

            var parameters = sut.GetParameterEnumerator(1, null).ToArray();
            Assert.AreEqual(1, parameters[0].Value);
            Assert.AreEqual(DBNull.Value, parameters[1].Value);
        }

        [TestMethod]
        public void InitializeParameters_NullValues_ParamLengthTwo()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("select * from aa where x=@1 and y= @2");

            var parameters = sut.GetParameterEnumerator(1, null).ToArray();
            Assert.AreEqual(2, parameters.Length);
        }

        [TestMethod]
        public void InitializeParameters_DbParameters_ShouldBeAddedAsDbParameters()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("select * from aa where x=@1 and y= @2");

            var parameters = sut.GetParameterEnumerator(new SqlParameter("@1", 1), null).ToArray();
            Assert.AreEqual(2, parameters.Length);
        }

        [TestMethod, ExpectedException(typeof(InvalidOperationException))]
        public void InitializeParameters_DbParametersInWrongOrder_ShouldThrow ()
        {
        	var sut = new SqlParser (testDbRes);
        	sut.Parse ("select * from aa where x=@1 and y= @2");

            sut.GetParameterEnumerator ("val_1", new SqlParameter ("@1", 1)).ToArray ();
        }

        [TestMethod]
        public void InitializeParameters_ExtraDbParameter_ShouldBeAppended()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("select * from aa where x=@1 and y= @2");

            var parameters = sut.GetParameterEnumerator("val_1", "val_2", new SqlParameter("@3", 1)).ToArray();
            Assert.AreEqual(3, parameters.Length);
        }

        [TestMethod]
        public void InitializeParameters_ParamWithNumber_ParamLengthOne()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("SELECT * WHERE id=@1");

            var parameters = sut.GetParameterEnumerator(1).ToArray();
            Assert.AreEqual(1, parameters.Length);
        }

        [TestMethod]
        public void InitializeParameters_ParamWithNumber_ParamLengthOne2()
        {
            var sut = new SqlParser(new OleDbResource(""));
            sut.Parse("SELECT Password FROM AppUser WHERE Username =?1");

            var parameters = sut.GetParameterEnumerator(1).ToArray();
            Assert.AreEqual(1, parameters.Length);
        }

        [TestMethod]
        public void ProcString_ValidParsing()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("Adresse_SelectByTelefonIdx /* @telefon @sessionid */");

            var parameters = sut.GetParameterEnumerator(1, 2).ToArray();
            Assert.AreEqual(2, parameters.Length);
            Assert.AreEqual("Adresse_SelectByTelefonIdx ", sut.Query);
            Assert.IsTrue(sut.CommandType == CommandType.StoredProcedure);
        }

        [TestMethod]
        public void ProcString_commalist_ValidParsing()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("Adresse_SelectByTelefonIdx /* @telefon, @sessionid */");

            var parameters = sut.GetParameterEnumerator(1, 2).ToArray();
            Assert.AreEqual(2, parameters.Length);
            Assert.IsTrue(sut.CommandType == CommandType.StoredProcedure);
        }

        [TestMethod]
        public void ParamnameWithParanthesisShouldBeParsed()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("Inser into table values(@id,@name,@phone)");
            var parameters = sut.GetParameterEnumerator(1, 2, 3).ToArray();

            Assert.AreEqual(3, parameters.Length);
        }

        [TestMethod]
        public void ParameterWUnderscore_Recognized()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("@fk_listid");

            var parameters = sut.GetParameterEnumerator(1).ToArray();
            Assert.AreEqual(1, parameters.Length);
            Assert.AreEqual("@fk_listid", parameters[0].ParameterName);
        }

        [TestMethod]
        public void Parse_ShouldKeepCaseOnParameternames()
        {
            var expected = "@AbCdE";

            var sut = new SqlParser(testDbRes);
            sut.Parse(expected);

            var parameters = sut.GetParameterEnumerator(1).ToArray();
            Assert.AreEqual(expected, parameters[0].ParameterName);
        }

        [TestMethod]
        public void IdentitySelectIsNotAParameter()
        {
            var sut = new SqlParser(testDbRes);
            sut.Parse("SELECT @@IDENTITY");

            var parameters = sut.GetParameterEnumerator().ToArray();
            Assert.AreEqual(0, parameters.Length);
        }

    }
}
